package com.quark.framework.database;

import com.quark.framework.configuration.QuarkConfigManager;
import com.quark.framework.i18n.QuarkI18nManager;
import com.quark.framework.ormapping.QuarkOrmEntity;
import com.quark.framework.ormapping.QuarkOrmProperty;
import com.quark.framework.utility.QuarkLog;
import com.quark.framework.utility.QuarkUtilStringConverter;
import com.quark.framework.utility.QuarkUtilStringConverter.QUARK_STRCONVERT_TYPE;

public class QuarkDBSqlHelper {

	public static String createSelect(Class<? extends QuarkOrmEntity> base, String limit)
	{
		String table = getTableName(getLowestClassName(base));
		String sql = "SELECT * FROM " + table + " WHERE " + limit;
		return sql;
	}
	
	public static String createSelect(Class<? extends QuarkOrmEntity> base, String[] fields, String limit)
	{
		String table = getTableName(getLowestClassName(base));
		String sql = "SELECT ";
		for(int i = 0; i < fields.length; i ++)
		{
			sql += fields[i];
			if(i < fields.length - 1)
				sql += ", ";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		sql += " FROM " + table + " WHERE " + limit;
		return sql;
	}
	
	public static String createSelect(Class<? extends QuarkOrmEntity> base, String[] fields, String limit, String sortBy, String startIndex, String fetchCount, boolean desc)
	{
		String sql = "";
		if(limit == null || limit.isEmpty())
		{
			limit = " 1 ";
		}
		sql += limit;
		if(sortBy != null && !sortBy.isEmpty())
		{
			sql += " ORDER BY " + sortBy;
		}
		if(desc)
		{
			sql += " DESC";
		}
		if(startIndex != null && fetchCount != null)
		{
			sql += " LIMIT " + startIndex + ", " + fetchCount;
		}
		else if(startIndex != null)
		{
			sql += " LIMIT " + startIndex + ", -1";
		}
		else if(fetchCount != null)
		{
			sql += " LIMIT " + fetchCount;
		}
		if(fields != null && fields.length > 0)
			return createSelect(base, fields, sql);
		else
			return createSelect(base, sql);
	}
	
	public static String createInsertIfNotExist(QuarkOrmEntity entity)
	{
		QuarkOrmProperty<?> updateKey = entity.getUpdateKey();
		if(updateKey == null)
			return null;
		String sql = createInsert(entity) + " ON DUPLICATE KEY UPDATE " + updateKey.getName() + " = " + updateKey.toSqlString();
		return sql;
	}
	
	public static String[] createInsertIfNotExist(QuarkOrmEntity[] entity)
	{
		String[] sql = new String[entity.length];
		for(int i = 0; i < entity.length; i ++)
		{
			sql[i] = createInsertIfNotExist(entity[i]);
		}
		return sql;
	}
	
	public static String createInsert(QuarkOrmEntity entity)
	{
		QuarkOrmProperty<?>[] properties = entity.getAllProperties();
		String table = getTableName(getLowestClassName(entity.getClass()));
		String sql = "INSERT INTO " + table + " (";
		String values = "";
		for(int i = 0; i < properties.length; i++)
		{
			if(properties[i].isUpdateKey())
				continue;
			sql += properties[i].getName();	
			if(properties[i].isEmpty())
				values += "NULL";
			else
				values += QuarkUtilStringConverter.convertString(properties[i].getValue(), QUARK_STRCONVERT_TYPE.CONVERT_SQL);
			if(i < properties.length - 1)
			{
				sql += ",";
				values += ",";
			}
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		if(values.endsWith(","))
			values = values.substring(0, values.length() - 1);
		sql += ") VALUES (" + values + ")";
		return sql;
	}
	
	public static String createReplace(QuarkOrmEntity[] entities)
	{
		if(entities == null || entities.length == 0)
		{
			return null;
		}
		String table = getTableName(getLowestClassName(entities.getClass().getComponentType()));
		String sql = "REPLACE INTO " + table + " (";
		String values = "";
		QuarkOrmProperty<?>[] properties = entities[0].getAllProperties();
		for(int i = 0; i < properties.length; i++)
		{
			if(properties[i].isUpdateKey())
				continue;
			sql += properties[i].getName();	
			if(i < properties.length - 1)
				sql += ",";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		for(int j = 0; j < entities.length; j++)
		{
			values += "(";
			for(int i = 0; i < properties.length; i++)
			{
				if(properties[i].isUpdateKey())
					continue;
				if(properties[i].isEmpty())
					values += "NULL";
				else
					values += QuarkUtilStringConverter.convertString(properties[i].getValue(),QUARK_STRCONVERT_TYPE.CONVERT_SQL);
				if(i < properties.length - 1)
				{
					values += ",";
				}
			}
			values += ")";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		if(values.endsWith(","))
			values = values.substring(0, values.length() - 1);
		sql += ") VALUES " + values + " ";
		return sql;		
	}
	
	public static String createInsert(QuarkOrmEntity[] entities)
	{
		if(entities == null || entities.length == 0)
		{
			return null;
		}
		String table = getTableName(getLowestClassName(entities.getClass().getComponentType()));
		String sql = "INSERT INTO " + table + " (";
		String values = "";
		QuarkOrmProperty<?>[] properties = entities[0].getAllProperties();
		for(int i = 0; i < properties.length; i++)
		{
			if(properties[i].isUpdateKey())
				continue;
			sql += properties[i].getName();	
			sql += ",";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		
		for(int j = 0; j < entities.length; j++)
		{
			values += "(";
			for(int i = 0; i < properties.length; i++)
			{
				if(properties[i].isUpdateKey())
					continue;
				sql += properties[i].getName();	
				if(properties[i].isEmpty())
					values += "NULL";
				else
					values += QuarkUtilStringConverter.convertString(properties[i].getValue(),QUARK_STRCONVERT_TYPE.CONVERT_SQL);
				if(i < properties.length - 1)
					sql += ",";
			}
			if(sql.endsWith(","))
				sql = sql.substring(0, sql.length() - 1);
			if(values.endsWith(","))
				values = values.substring(0, values.length() - 1);
			if(j < entities.length - 1)
			{
				values += " UNION ALL SELECT ";
			}
			values += ")";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		sql += ") VALUES " + values + " ";
		return sql;		
	}
	public static String createUpdate(QuarkOrmEntity entity, String limit)
	{
		String table = getLowestClassName(entity.getClass());
		QuarkOrmProperty<?>[] props = entity.getAllProperties();
		String sql = "UPDATE " + getTableName(table) + " SET ";
		for(int i = 0; i < props.length; i ++)
		{
			if(props[i].isUpdateKey())
				continue;
			if(!props[i].isModified())
				continue;			
			sql += props[i].getName() + " = " + props[i].toSqlString();
			if(i < props.length - 1)
				sql += ",";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		return sql + " WHERE " + limit;	
	}
	
	public static String createUpdate(QuarkOrmEntity entity)
	{
		QuarkOrmProperty<?> updateKey = entity.getUpdateKey();
		if(entity == null)
		{
			return null;
		}
		if(updateKey == null)
		{
			QuarkLog.logError("default update key not found while updating " + entity.getClass().getName());
			return null;
		}
		return createUpdate(entity, updateKey.getName() + " = " + updateKey.toSqlString());
	}
	
	public static String createGetCount(Class<? extends QuarkOrmEntity> type)
	{
		String sql = "SELECT COUNT(*) FROM " + getTableName(getLowestClassName(type));
		return sql;
	}
	
	public static String[] createUpdate(QuarkOrmEntity[] entity)
	{
		String[] sql = new String[entity.length];
		for(int i = 0; i < entity.length; i ++)
		{
			sql[i] = createUpdate(entity[i]);
		}
		return sql;
	}
	
	public static String createDelete(Class<? extends QuarkOrmEntity> entity, String limit)
	{
		if(entity == null)
			return null;
		String table = getTableName(entity.getSimpleName());
		String sql = "DELETE FROM " + table + " WHERE " + limit;
		return sql;
	}
	
	public static String createDelete(QuarkOrmEntity entity)
	{
		if(entity == null)
			return null;
		QuarkOrmProperty<?> prop = entity.getUpdateKey();
		if(prop == null)
		{
			QuarkLog.logError("updatekey not found when trying to delete " + entity.getClass().getSimpleName());
			return null;
		}
		
		return createDelete(entity.getClass(), prop.getName() + " = " + prop.toSqlString());
	}
	
	public static String createDelete(QuarkOrmEntity[] entity)
	//the Class of every entity should be the same
	{
		if(entity == null || entity.length == 0)
			return null;
		QuarkOrmProperty<?> updateKey = entity[0].getUpdateKey();
		if(updateKey == null)
		{
			QuarkLog.logError("updatekey not found when trying to detele " + entity[0].getClass().getSimpleName() + " group");
		}
		String sql = "DELETE * FROM " + getTableName(entity.getClass().getComponentType())+ " ";		
		for(int i = 0; i < entity.length; i++)
		{
			updateKey = entity[i].getUpdateKey();
			if(updateKey == null)
			{
				QuarkLog.logError("updatekey not found when trying to detele " + (i + 1) + " of " + entity[0].getClass().getSimpleName() + " group");
				continue;
			}
			sql += " " + updateKey.getName() + " = " + updateKey.toSqlString();
			if(i < entity.length - 1)
				sql += " OR ";
		}
		return sql;
	}
	
	public static String createSelectTemplate(Class<? extends QuarkOrmEntity> base)
	{
		try
		{
			QuarkOrmEntity inst = base.newInstance();
			QuarkOrmProperty<?> prop = inst.getUpdateKey();
			if(prop == null)
				return null;
			return createSelect(base, prop.getName() + " = ? ");
		}
		catch(Exception e)
		{
			QuarkLog.log(e);
			return null;
		}
	}
	
	public static String createInsertTemplate(Class<? extends QuarkOrmEntity> base)
	{
		try
		{
			QuarkOrmEntity entity = base.newInstance();
			QuarkOrmProperty<?>[] properties = entity.getAllProperties();
			String table = getTableName(getLowestClassName(base));
			String sql = "INSERT INTO " + table + " (";
			String values = "";
			for(int i = 0; i < properties.length; i++)
			{
				if(properties[i].isUpdateKey())
					continue;
				sql += properties[i].getName();	
				values += "?";
				if(i < properties.length - 1)
				{
					sql += ",";
					values += ",";
				}
			}
			if(sql.endsWith(","))
				sql = sql.substring(0, sql.length() - 1);
			if(values.endsWith(","))
				values = values.substring(0, values.length() - 1);
			sql += ") VALUES (" + values + ")";
			return sql;
		}catch(Exception e)
		{
			QuarkLog.log(e);
			return null;
		}
	}
	
	public static String createUpdateTemplate(QuarkOrmEntity entity)
	// partly update
	{
		String table = getTableName(getLowestClassName(entity.getClass()));
		QuarkOrmProperty<?>[] props = entity.getAllProperties();
		String sql = "UPDATE " + table + " SET ";
		for(int i = 0; i < props.length; i ++)
		{
			if(!props[i].isModified())
				continue;			
			sql += props[i].getName() + " = ? ";
			if(i < props.length - 1)
				sql += ",";
		}
		if(sql.endsWith(","))
			sql = sql.substring(0, sql.length() - 1);
		return sql;
	}
		
	public static String createCompleteUpdateTemplate(Class<? extends QuarkOrmEntity> base)
	// completely update
	{
		try
		{
			QuarkOrmEntity entity = base.newInstance();
			String table = getTableName(getLowestClassName(base));
			QuarkOrmProperty<?>[] props = entity.getAllProperties();
			String sql = "UPDATE " + table + " SET ";
			for(int i = 0; i < props.length; i ++)
			{
				if(props[i].isUpdateKey())
					continue;
				sql += props[i].getName() + " = ? ";
				if(i < props.length - 1)
					sql += ",";
			}
			return sql;
		}
		catch(Exception e)
		{
			QuarkLog.log(e);
			return null;
		}
		
		
	}
	
	public static String getLowestClassName(Class<?> entity) 
	{ 
		Class<?> lower = entity;
		while(!lower.getSuperclass().equals(QuarkOrmEntity.class))
		{
			lower = lower.getSuperclass();
		}
		return lower.getSimpleName();
	}
	
	public static String getTableName(Class<?> className)
	{
		return getTableName(className.getSimpleName());
	}
	
	public static String getTableName(String className)
	{
		QuarkDBConfiguration config = (QuarkDBConfiguration)QuarkConfigManager.getInstance().getConfiguration(QuarkDBConfiguration.class);
		return className + config.getDBTablePostFix(QuarkI18nManager.getInstance().getCurrentLanguage());
	}
}
